<?php
	/**
	 * 	教务人员登录
	 * 		@return $staff[staffname,staffpass]
	 */
	function selectStaff($staffname,$staffpass){
		$conn = mysqli_connect("localhost:3306","mebin","171110301cwb","sms");
		$sql = 	" select " .
				" 	staffname,staffpass " .
				" from " .
				" 	staff " .
				" where " .
				" 	staffname='$staffname' and staffpass='$staffpass' ";
		$result = mysqli_query($conn, $sql);
		$staff = null;
		if(mysqli_num_rows($result)==1){
			$rows = mysqli_fetch_assoc($result);
			$staff['staffname'] = $rows['staffname'];
			$staff['staffpass'] = $rows['staffpass'];
		}
		mysqli_close($conn);
		return $staff;
	}
	
	/**
	 * 插入学生学籍信息
	 * @return $flag
	 */
	function insertStuRoll($sturoll){
		$conn = mysqli_connect('localhost:3306',"mebin","171110301cwb","sms");
		
		mysqli_query($conn,"begin");
		$flag = true;
		
		$sql = 	" insert into student_roll " .
				" 	(sschool,spass,sname,snumber,ssex,ssys,senter,sgrad,sbirth,sidnumber,snative,sdesc,scollegeid,sgradeid,sclassid) " .
				" values " .
				" 	('{$sturoll["sschool"]}','{$sturoll["spass"]}','{$sturoll["sname"]}','{$sturoll["snumber"]}','{$sturoll["ssex"]}','{$sturoll["ssys"]}','{$sturoll["senter"]}','{$sturoll["sgrad"]}','{$sturoll["sbirth"]}','{$sturoll["sidnumber"]}','{$sturoll["snative"]}','{$sturoll["sdesc"]}','{$sturoll["scollegeid"]}','{$sturoll["sgradeid"]}','{$sturoll["sclassid"]}') " ;
		mysqli_query($conn,$sql);
		$stuid = mysqli_insert_id($conn);
		if(mysqli_errno($conn)){
			$flag = false;
			echo mysqli_error($conn);
		}
		//文件存入
		if($sturoll['simage']['error'] == 0){
			$target = $sturoll['simage']['tmp_name'];
			$file = fopen($target,'rb');
			$size = filesize($target);
			$content = fread($file,$size);
			$content = addslashes($content);
			fclose($file);
			$sql = 	" insert into student_roll_image" .
		 			" 	(stu_imgid,simage) " .
		 			" values " .
		   			" 	($stuid,'$content') " ;
			mysqli_query($conn,$sql);
			if(mysqli_errno($conn)){
				$flag = false;
				echo mysqli_error($conn);
			}
		}else{
			$flag = false;
		}
		
		if($flag){
			mysqli_query($conn,"commit");
		}else{
			mysqli_query($conn,"rollback");
		}
		
		mysqli_close($conn);
		return $flag;
	}

	/**
	 *  插入教师数据
	 * 		@return $flag
	 */
	function insertTeacher($teacher){
		$conn = mysqli_connect("localhost:3306","mebin","171110301cwb","sms");
		mysqli_query($conn,'begin');
		$flag = true;
		$sql =  " insert into teacher " .
				" 	(tschool,tname,tnumber,tsex,tattr,tcollegeid,tcourse,tphone,tdesc,tidnumber,tnative) " .
				" values " .
				"  	('{$teacher["tschool"]}','{$teacher["tname"]}','{$teacher["tnumber"]}','{$teacher["tsex"]}','{$teacher["tattr"]}','{$teacher["tcollegeid"]}','{$teacher["tcourse"]}','{$teacher["tphone"]}','{$teacher["tdesc"]}','{$teacher["tidnumber"]}','{$teacher["tnative"]}') ";
		mysqli_query($conn,$sql);
		if(mysqli_errno($conn)){
			$flag = false;
			echo mysqli_error($conn);
		}
		
		$tid = mysqli_insert_id($conn);
		$sql = 	" insert into course " .
				" 	(coursename,course_tid,course_attr) " .
				" values " .
				" 	('{$teacher["tcourse"]}',$tid,'{$teacher["tattr"]}') " ;
		mysqli_query($conn,$sql);
		if(mysqli_errno($conn)){
			$flag = false;
			echo mysqli_error($conn);
		}
		
		if($flag){
			mysqli_query($conn,'commit');
		}else{
			mysqli_query($conn,'rollback');
		}
		
		mysqli_close($conn);
		return $flag;
	}

	/**
	 * 输入公共课程和专业课程
	 */
	function insertSection_spepul($sec_spepuls){
		$conn = mysqli_connect("localhost:3306","mebin","171110301cwb","sms");
		$flag = true;
		$sql =  " select stuid from student_roll sr " .
				" where sr.scollegeid='{$sec_spepuls["sec_collegeid"]}' " .
				"     and sr.sgradeid='{$sec_spepuls["sec_gradeid"]}' " .
				"     and sr.sclassid='{$sec_spepuls["sec_classid"]}' " ;
		$result = mysqli_query($conn,$sql);
		$stuidss = [];
		while($rows = mysqli_fetch_assoc($result)){
			$stuidss[] = $rows;
		}
		foreach($stuidss as $stuids){
			foreach($stuids as $stuid){
				$sql =  " select stu_sec_spepul_id from student_section_spepul where stu_sec_spepul_id='$stuid' ";
				$result = mysqli_query($conn,$sql);
				if(mysqli_num_rows($result)){
					$sql =  " update student_section_spepul set " .
							"    spepul_sections = '{$sec_spepuls["spepul_sections"]}' " .
							" where stu_sec_spepul_id='$stuid' ";
					$result = mysqli_query($conn, $sql);
					if(mysqli_errno($conn)){
						$flag = false;
						echo mysqli_error($conn);
					}
				}else{
					$sql = 	" insert into student_section_spepul " .
							" 	(stu_sec_spepul_id,spepul_sections) " .
							" values " .
							" 	('$stuid','{$sec_spepuls["spepul_sections"]}') " ;
					$result = mysqli_query($conn, $sql);
					if(mysqli_errno($conn)){
						$flag = false;
						echo mysqli_error($conn);
					}
				}
			}
		}
			
		mysqli_close($conn);
		return $flag;
	}

	/**
	 * 根据学院，年级，班级查询学生信息
	 */
	function selectStuInforBycolAndgraAndcla($collegeid,$gradeid,$classid){
		$conn = mysqli_connect("localhost:3306","mebin","171110301cwb","SMS");
		$sql = 	" select " .
				" 	stuid,sname,snumber,spass,ssex,ssys,senter,sgrad,sbirth,sidnumber,snative,sdesc,collegename,gradename,classname " .
				" from " .
				" 	student_roll s left join college c " .
				" 	on s.scollegeid = c.collegeid " .
				" 	left join grade g " .
				" 	on s.sgradeid = g.gradeid " .
				" 	left join class c1 " .
				" 	on s.sclassid = c1.classid " .
				" where s.scollegeid = '$collegeid' and s.sgradeid = '$gradeid' and s.sclassid = '$classid' ";
		$result = mysqli_query($conn,$sql);
		if(mysqli_errno($conn)){
			$flag = false;
			echo mysqli_error($conn);
		}
		$students = [];
		while($rows = mysqli_fetch_assoc($result)){
			$students[] = $rows;
		}
		mysqli_close($conn);
		return $students;
	}
	
	
	/**
	 * 根据stuid查询信息
	 */
	function selectByStuid($stuid){
		$conn = mysqli_connect("localhost:3306","mebin","171110301cwb","sms");
		$sql =	" select " .
				" 	stuid,sname,snumber,scollegeid,sgradeid,sclassid,sschool,ssex,ssys,senter,sgrad,sbirth,sidnumber,snative,sdesc " .
				" from " .
				" 	student_roll,grade,class,college " .
				" where " .
				" 	stuid = '$stuid' " .
				"  	and student_roll.scollegeid = college.collegeid " .
				"  	and student_roll.sgradeid = grade.gradeid " .
				"  	and student_roll.sclassid = class.classid " ;
		$result = mysqli_query($conn, $sql);
		$student = null;
		if(mysqli_num_rows($result) == 1){
			$rows = mysqli_fetch_assoc($result);
			$student['stuid'] = $rows['stuid'];
			$student['sname'] = $rows['sname'];
			$student['snumber'] = $rows['snumber'];
			$student['scollegeid'] = $rows['scollegeid'];
			$student['sgradeid'] = $rows['sgradeid'];
			$student['sclassid'] = $rows['sclassid'];
			$student['sschool'] = $rows['sschool'];
			$student['ssex'] = $rows['ssex'];
			$student['ssys'] = $rows['ssys'];
			$student['senter'] = $rows['senter'];
			$student['sgrad'] = $rows['sgrad'];
			$student['sbirth'] = $rows['sbirth'];
			$student['sidnumber'] = $rows['sidnumber'];
			$student['snative'] = $rows['snative'];
			$student['sdesc'] = $rows['sdesc'];
		}	
		mysqli_close($conn);
		return $student;
	}

	/**
	 * 更新学生学籍信息
	 */
	function updateSturoll($sturoll){
		$conn = mysqli_connect("localhost:3306","mebin","171110301cwb","sms");
		mysqli_query($conn, 'begin');
		$flag = true;
		$sql =  " update student_roll set " .
				"   sname='{$sturoll["sname"]}',snumber='{$sturoll["snumber"]}',ssex='{$sturoll["ssex"]}',ssys='{$sturoll["ssys"]}',senter='{$sturoll["senter"]}',sgrad='{$sturoll["sgrad"]}',sbirth='{$sturoll["sbirth"]}',sidnumber='{$sturoll["sidnumber"]}',snative='{$sturoll["snative"]}',sdesc='{$sturoll["sdesc"]}',scollegeid='{$sturoll["scollegeid"]}',sgradeid='{$sturoll["sgradeid"]}',sclassid='{$sturoll["sclassid"]}' " .
				" where stuid = '{$sturoll["stuid"]}' ";
		mysqli_query($conn,$sql);
		if(mysqli_errno($conn)){
			$flag = false;
			echo mysqli_error($conn);
		}
		if($sturoll['simage']['error'] == 0){
			$target = $sturoll['simage']['tmp_name'];
			$file = fopen($target,'rb');
			$size = filesize($target);
			$content = fread($file,$size);
			$content = addslashes($content);
			fclose($file);
			$sql = " update student_roll_image set " .
			       "   simage = '$content " .
			       " where stu_imgid = '{$sturoll["stuid"]}' ";
			mysqli_query($conn,$sql);
			if(mysqli_errno($conn)){
				$flag = false;
				echo mysqli_error($conn);
			}
		}
		
		if($flag){
			mysqli_query($conn,"commit");
		}else{
			mysqli_query($conn,"rollback");
		}
		mysqli_close($conn);
		return $flag;
	}
	
	/**
	 * 根据stuid删除学生学籍信息
	 */
	function delBystuid($stuid){
		$conn = mysqli_connect("localhost:3306","mebin","171110301cwb","sms");
		mysqli_query($conn,"begin");
		$flag = true;
		$sql = " delete from student_roll_image where stu_imgid = $stuid ";
		mysqli_query($conn,$sql);
		if(mysqli_errno($conn)){
			$flag = false;
			echo mysqli_error();
		}
		$sql = " delete from student_roll where stuid = $stuid ";
		mysqli_query($conn,$sql);
		if(mysqli_errno($conn)){
			$flag = false;
			echo mysqli_error();
		}
		if($flag){
			mysqli_query($conn,'commit');
		}else{
			mysqli_query($conn,'rollback');
		}
		mysqli_close($conn);
		return $flag;
	}
	
	/**
	 *  查询学生成绩
	 */
	function selectStumark(){
		$conn = mysqli_connect("localhost:3306","mebin","171110301cwb","SMS");
		$sql =  " select mark_stuid,mark_tid,mark from mark order by mark_stuid";
		$result = mysqli_query($conn, $sql);
		$student_marks = [];
		while($rows = mysqli_fetch_assoc($result)){
			$student_marks[] = $rows;
		}
		mysqli_close($conn);
		return $student_marks;
	}
	
	/**
	 * 根据stuid和tid查询
	 */
	function selectInforBytidAndstuid($tid,$stuid){
		$conn = mysqli_connect("localhost:3306","mebin","171110301cwb","sms");
		$sql = 	" select stuid,sname,snumber,tname,tcourse,tattr,collegename " .
				"    from teacher t,college c,student_roll s " .
				" where t.tid = $tid and s.stuid = $stuid" .
				"    and t.tcollegeid=c.collegeid";
		$result = mysqli_query($conn, $sql);
		$teachers = null;
		if(mysqli_num_rows($result) == 1){
			$rows = mysqli_fetch_assoc($result);
			$teachers['stuid'] = $rows['stuid'];
			$teachers['sname'] = $rows['sname'];
			$teachers['snumber'] = $rows['snumber'];
			$teachers['tname'] = $rows['tname'];
			$teachers['tcourse'] = $rows['tcourse'];
			$teachers['tattr'] = $rows['tattr'];
			$teachers['collegename'] = $rows['collegename'];
		}
		mysqli_close($conn);
		return $teachers;
	}
	/**
	 * 根据老师所在学院查询老师信息
	 */
	function selectTeaByTcol($collegeid){
		$conn = mysqli_connect("localhost:3306","mebin","171110301cwb","SMS");
		$sql = 	" select " .
				" 	tid,tnumber,tname,tsex,tidnumber,collegename,tcourse,tphone,tnative,tdesc,tattr " .
				" from " .
				" 	teacher t left join college c " .
				" 	on t.tcollegeid = c.collegeid " .
				" where t.tcollegeid = '$collegeid' ";
		$result = mysqli_query($conn,$sql);
		$teachers = [];
		while($rows = mysqli_fetch_assoc($result)){
			$teachers[] = $rows;
		}
		mysqli_close($conn);
		return $teachers;
	}
	
	/**
	 * 根据tid删除老师信息
	 */
	function delBytid($tid){
		$conn = mysqli_connect("localhost:3306","mebin","171110301cwb","sms");
		mysqli_query($conn,"begin");
		$flag = true;
		$sql = " delete from course where course_tid = $tid ";
		mysqli_query($conn,$sql);
		if(mysqli_errno($conn)){
			$flag = false;
			echo mysqli_error();
		}
		$sql = " delete from teacher where tid = $tid ";
		mysqli_query($conn,$sql);
		if(mysqli_errno($conn)){
			$flag = false;
			echo mysqli_error();
		}
		if($flag){
			mysqli_query($conn,'commit');
		}else{
			mysqli_query($conn,'rollback');
		}
		mysqli_close($conn);
		return $flag;
	}
	
	/**
	 * 根据tid查询老师信息
	 */
	function selectBytid($tid){
		$conn = mysqli_connect("localhost:3306","mebin","171110301cwb","sms");
		$sql =	" select " .
				" 	tid,tschool,tname,tnumber,tsex,tcollegeid,tcourse,tphone,tdesc,tidnumber,tnative,coursename,tattr " .
				" from " .
				" 	teacher,college,course " .
				" where " .
				" 	tid = '$tid' " .
				"  	and teacher.tcollegeid = college.collegeid " .
				"   and course.course_tid = $tid";
		$result = mysqli_query($conn, $sql);
		$student = null;
		if(mysqli_num_rows($result) == 1){
			$rows = mysqli_fetch_assoc($result);
			$student['tid'] = $rows['tid'];
			$student['tschool'] = $rows['tschool'];
			$student['tname'] = $rows['tname'];
			$student['tnumber'] = $rows['tnumber'];
			$student['tsex'] = $rows['tsex'];
			$student['tcollegeid'] = $rows['tcollegeid'];
			$student['tcourse'] = $rows['tcourse'];
			$student['tphone'] = $rows['tphone'];
			$student['tdesc'] = $rows['tdesc'];
			$student['tidnumber'] = $rows['tidnumber'];
			$student['tnative'] = $rows['tnative'];
			$student['coursename'] = $rows['coursename'];
			$student['tattr'] = $rows['tattr'];
		}	
		mysqli_close($conn);
		return $student;
	}

	/**
	 * 更新老师信息
	 */
	function updateTea($teacher){
		$conn = mysqli_connect("localhost:3306","mebin","171110301cwb","sms");
		mysqli_query($conn,'begin');
		$flag = true;
		
		$sql =  " update course set " .
				"  coursename = '{$teacher["tcourse"]}' " .
				" where course_tid = '{$teacher["tid"]}' " ;
		mysqli_query($conn,$sql);
		if(mysqli_errno($conn)){
			$flag = false;
			echo mysqli_error($conn);
		}
		
		$sql =  " update teacher set " .
				" tname='{$teacher["tname"]}',tnumber='{$teacher["tnumber"]}',tsex='{$teacher["tsex"]}',tcollegeid='{$teacher["tcollegeid"]}',tcourse='{$teacher["tcourse"]}',tphone='{$teacher["tphone"]}',tdesc='{$teacher["tdesc"]}',tidnumber='{$teacher["tidnumber"]}',tnative='{$teacher["tnative"]}',tattr='{$teacher["tattr"]}' " . 
				" where tid = '{$teacher["tid"]}' ";
		mysqli_query($conn,$sql);
		if(mysqli_errno($conn)){
			$flag = false;
			echo mysqli_error($conn);
		}
		
		if($flag){
			mysqli_query($conn,'commit');
		}else{
			mysqli_query($conn,'rollback');
		}
		
		mysqli_close($conn);
		return $flag;
	}

?>